﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_SelfRelationshipCheck] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	
	--PRINT 'Ensure all students have a Self_Child Relationship with themselves'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Self_Child"' 
		 + ',"Entity1ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,
		 RS.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT DISTINCT P.EntityID,P.GradYear FROM {Person AS P} LEFT JOIN {Student AS S}
				ON S.Entity1ID = P.EntityID
			LEFT JOIN {APPLIED AS A} ON P.EntityID = A.Entity1ID 
			WHERE (NOT A.Entity2ID IS NULL OR NOT S.Entity2ID IS NULL)''') AS RS
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
		ON 1>0
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {SELF_CHILD}''') AS S
		ON S.Entity1ID = RS.EntityID
	WHERE S.Entity2ID IS NULL AND (RS.GradYear > CYT.OldRVAYear OR RS.GradYear IS NULL)
	
	--PRINT 'Ensure all adults have a Self_Adult Relationship with themselves'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Self_Adult"' 
		 + ',"Entity1ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,
		 RS.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT DISTINCT P.EntityID,P.GradYear FROM {Person AS P}
			''') AS RS
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
		ON 1>0
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {SELF_ADULT}''') AS S
		ON S.Entity1ID = RS.EntityID
	WHERE S.Entity2ID IS NULL AND (RS.GradYear <= CYT.OldRVAYear OR RS.GradYear IS NULL)
	
	--PRINT 'Remove Self_Child relationships from persons that have a Self-Adult relationship'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True","ShortRelationshipName":"Self_Child"' 
		 + ',"Entity1ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,
		 RS.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT DISTINCT P.EntityID,P.GradYear FROM {Person AS P}
			''') AS RS
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {SELF_CHILD}''') AS SC
		ON SC.Entity1ID = RS.EntityID
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
			@NOSQL = ''SELECT * FROM {SELF_ADULT}''') AS SA
		ON SA.Entity1ID = RS.EntityID

	RETURN 
END